using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace SS.core
{
	internal class products
	{
		private Func objFunc = new Func();
		public products()
		{
			Program.strConnectionString = this.objFunc.strConnectionString;
		}
		public void loadToListView(DataGridView lv, string searchFor, string parentID, string gID, string barCode)
		{
			lv.AutoGenerateColumns = false;
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			string text = "SELECT 0 AS TT,PID,P_Name,P_PriceBase,P_PriceTrade,P_PriceRetail FROM [TabProducts] WHERE 1 = 1 ";
			if (searchFor != "")
			{
				text = text + " AND [P_Name] LIKE N'%" + searchFor + "%' ";
			}
			else
			{
				if (barCode != "")
				{
					text = text + " AND [BarCode] LIKE N'%" + barCode + "%' ";
				}
				else
				{
					if (parentID != "0")
					{
						text = text + " AND GpID=" + parentID;
					}
					if (gID != "0")
					{
						text = text + " AND  GpParentID= " + gID + "";
					}
				}
			}
			text += " ORDER BY [P_Name]";
			SqlCommand selectCommand = new SqlCommand(text, sqlConnection);
			sqlConnection.Open();
			SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectCommand);
			DataTable dataTable = new DataTable();
			sqlDataAdapter.Fill(dataTable);
			lv.DataSource = dataTable.DefaultView;
			sqlConnection.Close();
			sqlConnection.Dispose();
			sqlDataAdapter.Dispose();
			dataTable.Dispose();
		}
		public void getInfo(string pID, ComboBox cbBgParent, TextBox txtBarcode, ComboBox cbBGpParent, ComboBox cbBCompany, TextBox txtName, ComboBox cbbUnitProduct, TextBox txtInputPrice, TextBox txtSalePrice, TextBox txtreTailPrice, TextBox txtRequestNumber, TextBox txtMinimumNumber, ComboBox cbBGTGT, ComboBox cbPromote, TextBox txtPromote)
		{
			GProduct gProduct = new GProduct();
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			SqlCommand sqlCommand = new SqlCommand();
			sqlCommand.Connection = sqlConnection;
			sqlCommand.CommandText = "SELECT * FROM TabProducts WHERE PID = " + pID;
			sqlConnection.Open();
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					try
					{
						cbBgParent.SelectedValue = gProduct.GetValue("GpParentID", "TabGproducts", "GpID", sqlDataReader["GpID"].ToString());
					}
					catch
					{
						cbBgParent.SelectedValue = "0";
					}
					txtBarcode.Text = sqlDataReader["BarCode"].ToString();
					string text = sqlDataReader["GpID"].ToString();
					cbBgParent.SelectedValue = sqlDataReader["GpID"].ToString();
					cbBGpParent.SelectedValue = sqlDataReader["GpParentID"].ToString();
					try
					{
						cbBCompany.SelectedValue = sqlDataReader["ManID"].ToString();
					}
					catch
					{
						cbBCompany.SelectedIndex = 0;
					}
					txtName.Text = sqlDataReader["P_Name"].ToString();
					cbbUnitProduct.SelectedValue = sqlDataReader["P_DonViTinh"].ToString();
					txtInputPrice.Text = string.Format(Program.lenAffterDecimal, double.Parse(sqlDataReader["P_PriceBase"].ToString()));
					txtSalePrice.Text = string.Format(Program.lenAffterDecimal, double.Parse(sqlDataReader["P_PriceTrade"].ToString()));
					txtreTailPrice.Text = string.Format(Program.lenAffterDecimal, double.Parse(sqlDataReader["P_PriceRetail"].ToString()));
					txtRequestNumber.Text = sqlDataReader["P_Order"].ToString();
					txtMinimumNumber.Text = sqlDataReader["P_Inventory"].ToString();
					cbBGTGT.Text = double.Parse(sqlDataReader["GTGT_ID"].ToString()).ToString() + "%";
					txtPromote.Text = sqlDataReader["P_PromoteMoney"].ToString();
					cbPromote.Text = double.Parse(sqlDataReader["P_PromotePecent"].ToString()).ToString() + "%";
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
		}
		public bool checkBarcode(string barcodeID)
		{
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			SqlCommand sqlCommand = new SqlCommand();
			sqlCommand.Connection = sqlConnection;
			sqlCommand.CommandText = "SELECT Barcode FROM TabProducts WHERE [Barcode]='" + barcodeID + "'";
			sqlConnection.Open();
			bool result = false;
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				result = true;
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
		public bool checkPacket(string pid, string packet)
		{
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			SqlCommand sqlCommand = new SqlCommand();
			sqlCommand.Connection = sqlConnection;
			sqlCommand.CommandText = string.Concat(new string[]
			{
				"SELECT * FROM [TabDetailBill] WHERE [Pid] = ",
				pid,
				" AND [packet] = '",
				packet,
				"' AND billID IN (SELECT ID FROM TabBill WHERE [TypeInput] IN ('IN1','IN2','IN3'))"
			});
			sqlConnection.Open();
			bool result = false;
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				result = true;
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
		public int del(string id)
		{
			int result;
			try
			{
				SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
				sqlConnection.Open();
				SqlCommand sqlCommand = new SqlCommand("DelProducts", sqlConnection);
				sqlCommand.CommandType = CommandType.StoredProcedure;
				sqlCommand.Parameters.Add("ID", SqlDbType.VarChar);
				sqlCommand.Parameters["ID"].Value = id;
				sqlCommand.ExecuteNonQuery();
				sqlConnection.Close();
				sqlConnection.Dispose();
				result = 0;
			}
			catch
			{
				result = 1;
			}
			return result;
		}
		public string getUnit(string pID)
		{
			string result = "0";
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			SqlCommand sqlCommand = new SqlCommand();
			sqlCommand.Connection = sqlConnection;
			sqlCommand.CommandText = "SELECT [P_DonViTinh] FROM TabProducts WHERE [Pid] = " + pID;
			sqlConnection.Open();
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					try
					{
						result = sqlDataReader["P_DonViTinh"].ToString();
					}
					catch
					{
						result = "0";
					}
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
		public string getName(string pID)
		{
			string result = "";
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			SqlCommand sqlCommand = new SqlCommand();
			sqlCommand.Connection = sqlConnection;
			sqlCommand.CommandText = "SELECT [P_Name] FROM TabProducts WHERE [Pid] = " + pID;
			sqlConnection.Open();
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					try
					{
						result = sqlDataReader["P_Name"].ToString();
					}
					catch
					{
						result = "";
					}
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
		public string getUsage(string pID)
		{
			string result = "";
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			SqlCommand sqlCommand = new SqlCommand();
			sqlCommand.Connection = sqlConnection;
			sqlCommand.CommandText = "SELECT [Usage] FROM TabProducts WHERE [Pid] = " + pID;
			sqlConnection.Open();
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					try
					{
						result = sqlDataReader["Usage"].ToString();
					}
					catch
					{
						result = "";
					}
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
		public bool checkForSuppend(string pID, string PacketID)
		{
			bool result = false;
			try
			{
				SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
				SqlCommand sqlCommand = new SqlCommand();
				sqlCommand.Connection = sqlConnection;
				sqlCommand.CommandText = string.Concat(new string[]
				{
					"SELECT * FROM [TabListSuppend] WHERE ([Pid] = ",
					pID,
					" AND [Packet] = '",
					PacketID,
					"') OR ([Pid] = ",
					pID,
					" AND [Packet] = 0)"
				});
				sqlConnection.Open();
				SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
				if (sqlDataReader.HasRows)
				{
					while (sqlDataReader.Read())
					{
						result = true;
					}
				}
				sqlDataReader.Close();
				sqlDataReader.Dispose();
				sqlConnection.Close();
				sqlConnection.Dispose();
			}
			catch
			{
			}
			return result;
		}
		public int getDayLive(string pID)
		{
			int result = 0;
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			SqlCommand sqlCommand = new SqlCommand();
			sqlCommand.Connection = sqlConnection;
			sqlCommand.CommandText = "SELECT [DayLive] FROM [TabProducts] WHERE [Pid] = " + pID;
			sqlConnection.Open();
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					try
					{
						result = int.Parse(sqlDataReader["DayLive"].ToString());
					}
					catch
					{
						result = 0;
					}
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
		public string getIngredient(string pid)
		{
			string text = "";
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			sqlConnection.Open();
			try
			{
				SqlCommand sqlCommand = sqlConnection.CreateCommand();
				sqlCommand.CommandText = "SELECT Name FROM TabIngredient WHERE ID IN (SELECT iID FROM [TabProductIngedient] WHERE [Pid] = " + pid + ")";
				SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
				if (sqlDataReader.HasRows)
				{
					while (sqlDataReader.Read())
					{
						text = text + sqlDataReader["Name"].ToString() + ", ";
					}
					text = " ( " + text.Remove(text.Length - 1, 1) + " ) ";
				}
				sqlDataReader.Close();
				sqlDataReader.Dispose();
			}
			catch
			{
			}
			sqlConnection.Close();
			sqlConnection.Dispose();
			return text;
		}
		public double getPriceInAVG(string pID)
		{
			double result = 0.0;
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			SqlCommand sqlCommand = new SqlCommand();
			sqlCommand.Connection = sqlConnection;
			sqlCommand.CommandText = "SELECT [P_PriceBase] FROM TabProducts WHERE [Pid] = " + pID;
			sqlConnection.Open();
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					try
					{
						result = double.Parse(sqlDataReader["P_PriceBase"].ToString());
					}
					catch
					{
						result = 0.0;
					}
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
		public double getPriceOutRetailAVG(string pID)
		{
			double result = 0.0;
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			SqlCommand sqlCommand = new SqlCommand();
			sqlCommand.Connection = sqlConnection;
			sqlCommand.CommandText = "SELECT [P_PriceRetail] FROM TabProducts WHERE [Pid] = " + pID;
			sqlConnection.Open();
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					try
					{
						result = double.Parse(sqlDataReader["P_PriceRetail"].ToString());
					}
					catch
					{
						result = 0.0;
					}
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
		public double getPriceOutputAVG(string pID)
		{
			double result = 0.0;
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			SqlCommand sqlCommand = new SqlCommand();
			sqlCommand.Connection = sqlConnection;
			sqlCommand.CommandText = "SELECT [P_PriceTrade] FROM TabProducts WHERE [Pid] = " + pID;
			sqlConnection.Open();
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					try
					{
						result = double.Parse(sqlDataReader["P_PriceTrade"].ToString());
					}
					catch
					{
						result = 0.0;
					}
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
		public double getPriceOutRetailBase(string pID, string Packet)
		{
			double result = 0.0;
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			SqlCommand sqlCommand = new SqlCommand();
			sqlCommand.Connection = sqlConnection;
			sqlCommand.CommandText = "SELECT [productPrice2] FROM [TabBill],[TabDetailBill] WHERE [TabBill].[ID] = [TabDetailBill].[billID] AND [TabBill].[TypeInput] IN ('IN1','IN2','IN3') AND [TabDetailBill].[Packet] = '" + Packet + "' AND [TabDetailBill].[Pid] = " + pID;
			sqlConnection.Open();
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			while (sqlDataReader.Read())
			{
				try
				{
					result = double.Parse(sqlDataReader["productPrice2"].ToString());
				}
				catch
				{
					result = 0.0;
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
		public double getPriceOutRetailBase(string pID)
		{
			double result = 0.0;
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			SqlCommand sqlCommand = new SqlCommand();
			sqlCommand.Connection = sqlConnection;
			sqlCommand.CommandText = "SELECT TOP 1 [productPrice2] FROM [TabBill],[TabDetailBill] WHERE [TabBill].[ID] = [TabDetailBill].[billID] AND [TabBill].[TypeInput] IN ('IN1','IN2','IN3') AND [TabDetailBill].[Pid] = " + pID + " ORDER BY TabBill.createDate DESC";
			sqlConnection.Open();
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			while (sqlDataReader.Read())
			{
				try
				{
					result = double.Parse(sqlDataReader["productPrice2"].ToString());
				}
				catch
				{
					result = 0.0;
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
		public double getPriceOutBase(string pID, string Packet)
		{
			double result = 0.0;
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			SqlCommand sqlCommand = new SqlCommand();
			sqlCommand.Connection = sqlConnection;
			sqlCommand.CommandText = "SELECT [productPrice1] FROM [TabBill],[TabDetailBill] WHERE [TabBill].[ID] = [TabDetailBill].[billID] AND [TabBill].[TypeInput] IN ('IN1','IN2','IN3') AND [TabDetailBill].[Packet] = '" + Packet + "' AND [TabDetailBill].[Pid] = " + pID;
			sqlConnection.Open();
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			while (sqlDataReader.Read())
			{
				try
				{
					result = double.Parse(sqlDataReader["productPrice1"].ToString());
				}
				catch
				{
					result = 0.0;
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
		public double getPricePublicBase(string pID, string Packet)
		{
			double result = 0.0;
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			SqlCommand sqlCommand = new SqlCommand();
			sqlCommand.Connection = sqlConnection;
			sqlCommand.CommandText = "SELECT [PublicPrice] FROM [TabBill],[TabDetailBill] WHERE [TabBill].[ID] = [TabDetailBill].[billID] AND [TabBill].[TypeInput] IN ('IN1','IN2','IN3') AND [TabDetailBill].[Packet] = '" + Packet + "' AND [TabDetailBill].[Pid] = " + pID;
			sqlConnection.Open();
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			while (sqlDataReader.Read())
			{
				try
				{
					result = double.Parse(sqlDataReader["PublicPrice"].ToString());
				}
				catch
				{
					result = 0.0;
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
		public double getPriceInAVGInAnRan(string pID, DateTime Date1, DateTime Date2)
		{
			double result = 0.0;
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			SqlParameter sqlParameter = new SqlParameter("objDate1", SqlDbType.DateTime);
			SqlParameter sqlParameter2 = new SqlParameter("objDate2", SqlDbType.DateTime);
			SqlCommand sqlCommand = new SqlCommand();
			sqlParameter.Value = DateTime.Parse(Date1.ToString().Substring(0, 10) + " 00:00:00 AM");
			sqlParameter2.Value = DateTime.Parse(Date2.ToString().Substring(0, 10) + " 11:59:59 PM");
			sqlCommand.Parameters.Add(sqlParameter);
			sqlCommand.Parameters.Add(sqlParameter2);
			sqlCommand.Connection = sqlConnection;
			sqlCommand.CommandText = "SELECT SUM(ProductPrice)/COUNT(*) AS AVGPrice FROM [TabDetailBill],[TabBill] WHERE [TypeInput] IN ('IN1','IN2','IN3') AND [TabDetailBill].[PID] = " + pID + " AND [TabDetailBill].[billID] = [TabBill].[ID] AND [TabBill].[CreateDate] BETWEEN @objDate1 AND @objDate2";
			sqlConnection.Open();
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					try
					{
						result = double.Parse(sqlDataReader["AVGPrice"].ToString());
					}
					catch
					{
						result = 0.0;
					}
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
		public double getPriceInMaxInAnRan(string pID, DateTime Date1, DateTime Date2)
		{
			double result = 0.0;
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			SqlParameter sqlParameter = new SqlParameter("objDate1", SqlDbType.DateTime);
			SqlParameter sqlParameter2 = new SqlParameter("objDate2", SqlDbType.DateTime);
			SqlCommand sqlCommand = new SqlCommand();
			sqlParameter.Value = DateTime.Parse(Date1.ToString().Substring(0, 10) + " 00:00:00 AM");
			sqlParameter2.Value = DateTime.Parse(Date2.ToString().Substring(0, 10) + " 11:59:59 PM");
			sqlCommand.Parameters.Add(sqlParameter);
			sqlCommand.Parameters.Add(sqlParameter2);
			sqlCommand.Connection = sqlConnection;
			sqlCommand.CommandText = "SELECT MAX(ProductPrice) AS MaxPrice FROM [TabDetailBill],[TabBill] WHERE [TypeInput] IN ('IN1','IN2','IN3') AND [TabDetailBill].[PID] = " + pID + " AND [TabDetailBill].[billID] = [TabBill].[ID] AND [TabBill].[CreateDate] BETWEEN @objDate1 AND @objDate2";
			sqlConnection.Open();
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					try
					{
						result = double.Parse(sqlDataReader["MaxPrice"].ToString());
					}
					catch
					{
						result = 0.0;
					}
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
		public double getPriceInMinInAnRan(string pID, DateTime Date1, DateTime Date2)
		{
			double result = 0.0;
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			SqlParameter sqlParameter = new SqlParameter("objDate1", SqlDbType.DateTime);
			SqlParameter sqlParameter2 = new SqlParameter("objDate2", SqlDbType.DateTime);
			SqlCommand sqlCommand = new SqlCommand();
			sqlParameter.Value = DateTime.Parse(Date1.ToString().Substring(0, 10) + " 00:00:00 AM");
			sqlParameter2.Value = DateTime.Parse(Date2.ToString().Substring(0, 10) + " 11:59:59 PM");
			sqlCommand.Parameters.Add(sqlParameter);
			sqlCommand.Parameters.Add(sqlParameter2);
			sqlCommand.Connection = sqlConnection;
			sqlCommand.CommandText = "SELECT MIN(ProductPrice) AS MinPrice FROM [TabDetailBill],[TabBill] WHERE [TypeInput] IN ('IN1','IN2','IN3') AND [TabDetailBill].[PID] = " + pID + " AND [TabDetailBill].[billID] = [TabBill].[ID] AND [TabBill].[CreateDate] BETWEEN @objDate1 AND @objDate2";
			sqlConnection.Open();
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					try
					{
						result = double.Parse(sqlDataReader["MinPrice"].ToString());
					}
					catch
					{
						result = 0.0;
					}
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
		public double getPriceInCurrent(string pID)
		{
			double result = 0.0;
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			SqlCommand sqlCommand = new SqlCommand();
			sqlCommand.Connection = sqlConnection;
			sqlCommand.CommandText = "SELECT TOP 1 ProductPrice FROM [TabDetailBill],[TabBill] WHERE [TypeInput] IN ('IN1','IN2','IN3') AND [TabDetailBill].[PID] = " + pID + " AND [TabDetailBill].[billID] = [TabBill].[ID] ORDER BY [TabBill].[CreateDate] DESC";
			sqlConnection.Open();
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					try
					{
						result = double.Parse(sqlDataReader["ProductPrice"].ToString());
					}
					catch
					{
						result = 0.0;
					}
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
		public double getPriceOutTradeCurrent(string pID)
		{
			double result = 0.0;
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			SqlCommand sqlCommand = new SqlCommand();
			sqlCommand.Connection = sqlConnection;
			sqlCommand.CommandText = "SELECT TOP 1 ProductPrice1 FROM [TabDetailBill],[TabBill] WHERE [TypeInput] IN ('IN1','IN2','IN3') AND [TabDetailBill].[PID] = " + pID + " AND [TabDetailBill].[billID] = [TabBill].[ID] ORDER BY [TabBill].[CreateDate] DESC";
			sqlConnection.Open();
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					try
					{
						result = double.Parse(sqlDataReader["ProductPrice1"].ToString());
					}
					catch
					{
						result = 0.0;
					}
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
		public double getPriceOutRetailCurrent(string pID)
		{
			double result = 0.0;
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			SqlCommand sqlCommand = new SqlCommand();
			sqlCommand.Connection = sqlConnection;
			sqlCommand.CommandText = "SELECT TOP 1 ProductPrice2 FROM [TabDetailBill],[TabBill] WHERE [TypeInput] IN ('IN1','IN2','IN3') AND [TabDetailBill].[PID] = " + pID + " AND [TabDetailBill].[billID] = [TabBill].[ID] ORDER BY [TabBill].[CreateDate] DESC";
			sqlConnection.Open();
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					try
					{
						result = double.Parse(sqlDataReader["ProductPrice2"].ToString());
					}
					catch
					{
						result = 0.0;
					}
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
		public string getNationalID(string pID, string Packet)
		{
			string result = "";
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			SqlCommand sqlCommand = new SqlCommand();
			sqlCommand.Connection = sqlConnection;
			sqlCommand.CommandText = "SELECT [NationalID] FROM [TabBill],[TabDetailBill] WHERE [TabBill].[ID] = [TabDetailBill].[billID] AND [TabBill].[TypeInput] IN ('IN1','IN2','IN3') AND [TabDetailBill].[Packet] = '" + Packet + "'";
			sqlConnection.Open();
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			while (sqlDataReader.Read())
			{
				result = sqlDataReader["NationalID"].ToString();
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
		public int countProductWarningDate()
		{
			int warningDay = this.objFunc.getWarningDay();
			int result = 0;
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			sqlConnection.Open();
			SqlCommand sqlCommand = sqlConnection.CreateCommand();
			sqlCommand.CommandText = "SELECT COUNT(*) AS CountItem FROM [TabTotalBalancer] WHERE ([TabTotalBalancer].[ProductExpiryDate] - getdate() < " + warningDay.ToString() + ") AND [TabTotalBalancer].[productNumberIn] > [TabTotalBalancer].[productNumberOut]";
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			while (sqlDataReader.Read())
			{
				try
				{
					result = int.Parse(sqlDataReader["CountItem"].ToString());
				}
				catch
				{
				}
			}
			sqlDataReader.Close();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
		public int countProductWarningDate_Backup_06_01_2010()
		{
			int warningDay = this.objFunc.getWarningDay();
			int result = 0;
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			sqlConnection.Open();
			SqlCommand sqlCommand = sqlConnection.CreateCommand();
			sqlCommand.CommandText = "SELECT COUNT([TabDetailBill].[Pid]) AS CountItem FROM [TabDetailBill],[TabBill] WHERE ([TabDetailBill].[ProductExpiryDate] - getdate() < " + warningDay.ToString() + ") AND [TabDetailBill].[billID] = [TabBill].[ID] AND  [TypeInput] IN ('IN1','IN2','IN3')";
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			while (sqlDataReader.Read())
			{
				try
				{
					result = int.Parse(sqlDataReader["CountItem"].ToString());
				}
				catch
				{
				}
			}
			sqlDataReader.Close();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
		public int countProductWarningNumber(string currWID)
		{
			int num = 0;
			int warningNumberProduct = this.objFunc.getWarningNumberProduct();
			try
			{
				SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
				sqlConnection.Open();
				SqlCommand sqlCommand = sqlConnection.CreateCommand();
				sqlCommand.CommandText = "SELECT * FROM [TabTotalBalancer] ORDER BY [ProductName]";
				SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
				while (sqlDataReader.Read())
				{
					string sqlQuery = "SELECT (SUM([ProductNumberIn]) - SUM([ProductNumberOut])) AS totalBalancer FROM [TabTotalBalancer] WHERE [PID] = " + sqlDataReader["PID"].ToString();
					float num2 = float.Parse(this.numTotalBalancerOfProduct(sqlQuery));
					float num3 = float.Parse(sqlDataReader["MinBalance"].ToString());
					float num4 = float.Parse(sqlDataReader["MaxBalance"].ToString());
					if (num2 - num3 <= float.Parse(warningNumberProduct.ToString()))
					{
						num++;
					}
				}
				sqlDataReader.Close();
				sqlConnection.Close();
				sqlConnection.Dispose();
			}
			catch
			{
				num = 0;
			}
			return num;
		}
		public int countProductWarningNumber_Backup_06_01_2010(string currWID)
		{
			int num = 0;
			int warningNumberProduct = this.objFunc.getWarningNumberProduct();
			try
			{
				SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
				sqlConnection.Open();
				SqlCommand sqlCommand = sqlConnection.CreateCommand();
				sqlCommand.CommandText = "SELECT [TabDetailBill].[Pid],[TabDetailBill].[ProductName],[TabDetailBill].[ProductNumber],[TabDetailBill].[ProductUnit] FROM [TabDetailBill],[TabBill] WHERE [TabDetailBill].[billID] = [TabBill].[ID] AND [TypeInput] IN ('IN1','IN2','IN3') ORDER BY [ProductName]";
				SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
				while (sqlDataReader.Read())
				{
					string sqlQuery = "SELECT SUM([TabDetailBill].[ProductNumber]) AS totalInput FROM [TabDetailBill],[TabBill] WHERE [TabDetailBill].[billID] = [TabBill].[ID] AND [TypeInput] IN ('IN1','IN2','IN3') AND [TabDetailBill].[PID] = " + sqlDataReader["PID"].ToString();
					float num2 = float.Parse(this.numOutput(sqlDataReader["Pid"].ToString(), currWID));
					float num3 = float.Parse(this.numMinBalance(sqlDataReader["Pid"].ToString()));
					float num4 = float.Parse(this.numMaxBalance(sqlDataReader["Pid"].ToString()));
					float num5 = float.Parse(this.numTotalInput(sqlQuery)) - num2;
					if (num5 - num3 <= float.Parse(warningNumberProduct.ToString()))
					{
						num++;
					}
				}
				sqlDataReader.Close();
				sqlConnection.Close();
				sqlConnection.Dispose();
			}
			catch
			{
				num = 0;
			}
			return num;
		}
		public float getTotalBalancerOfProduct(string Pid, string currWID)
		{
			string text;
			if (currWID == "0")
			{
				text = "[WID] <> '" + currWID + "'";
			}
			else
			{
				text = "[WID] = '" + currWID + "'";
			}
			float num = 0f;
			string sqlQuery = string.Concat(new string[]
			{
				"SELECT ISNULL((SELECT SUM(productNumber) FROM TabDetailBill WHERE BillID IN (SELECT ID FROM TabBill WHERE ",
				text,
				" AND [TypeInput] IN ('IN1','IN2','IN3')) AND [PID] = ",
				Pid,
				"),0) AS totalInput"
			});
			float num2 = float.Parse(this.numTotalInput(sqlQuery)) - num;
			num = float.Parse(this.numOutput(Pid, currWID));
			return float.Parse(this.numTotalInput(sqlQuery)) - num;
		}
		private string numTotalBalancerOfProduct(string sqlQuery)
		{
			string text = "0";
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			sqlConnection.Open();
			SqlCommand sqlCommand = sqlConnection.CreateCommand();
			sqlCommand.CommandText = sqlQuery;
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					text = sqlDataReader["totalBalancer"].ToString();
				}
			}
			else
			{
				text = "0";
			}
			sqlDataReader.Close();
			sqlConnection.Close();
			sqlConnection.Dispose();
			if (text.Trim() == "")
			{
				text = "0";
			}
			return text;
		}
		private string numOutput(string PID, string currWhID)
		{
			string text = "0";
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			sqlConnection.Open();
			SqlCommand sqlCommand = sqlConnection.CreateCommand();
			sqlCommand.CommandText = string.Concat(new string[]
			{
				"SELECT ISNULL((SELECT SUM(productNumber/Rate) FROM TabDetailBill WHERE BillID IN (SELECT ID FROM TabBill WHERE [WID] = '",
				currWhID,
				"' AND [TypeInput] IN ('OUT1','OUT2','OUT3','OUT4')) AND [PID] = ",
				PID,
				"),0) - ISNULL((SELECT SUM(productNumber) FROM TabDetailBill WHERE BillID IN (SELECT ID FROM TabBill WHERE [WID] = '",
				currWhID,
				"' AND [TypeInput] IN ('IN3')) AND [PID] = ",
				PID,
				"),0) AS numOutput"
			});
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					text = sqlDataReader["numOutput"].ToString();
				}
			}
			else
			{
				text = "0";
			}
			sqlDataReader.Close();
			sqlConnection.Close();
			sqlConnection.Dispose();
			if (text.Trim() == "")
			{
				text = "0";
			}
			return text;
		}
		private string numTotalInput(string sqlQuery)
		{
			string text = "0";
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			sqlConnection.Open();
			SqlCommand sqlCommand = sqlConnection.CreateCommand();
			sqlCommand.CommandText = sqlQuery;
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					text = sqlDataReader["totalInput"].ToString();
				}
			}
			else
			{
				text = "0";
			}
			sqlDataReader.Close();
			sqlConnection.Close();
			sqlConnection.Dispose();
			if (text.Trim() == "")
			{
				text = "0";
			}
			return text;
		}
		private string numMinBalance(string PID)
		{
			string text = "0";
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			sqlConnection.Open();
			SqlCommand sqlCommand = sqlConnection.CreateCommand();
			sqlCommand.CommandText = "SELECT [MinBalance] FROM [TabProducts] WHERE [PID] = " + PID;
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					text = sqlDataReader["MinBalance"].ToString();
				}
			}
			else
			{
				text = "0";
			}
			sqlDataReader.Close();
			sqlConnection.Close();
			sqlConnection.Dispose();
			if (text.Trim() == "")
			{
				text = "0";
			}
			return text;
		}
		private string numMaxBalance(string PID)
		{
			string text = "0";
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			sqlConnection.Open();
			SqlCommand sqlCommand = sqlConnection.CreateCommand();
			sqlCommand.CommandText = "SELECT [MaxBalance] FROM [TabProducts] WHERE [PID] = " + PID;
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					text = sqlDataReader["MaxBalance"].ToString();
				}
			}
			else
			{
				text = "0";
			}
			sqlDataReader.Close();
			sqlConnection.Close();
			sqlConnection.Dispose();
			if (text.Trim() == "")
			{
				text = "0";
			}
			return text;
		}
		private bool checkProductNameInDataGridView(string pName, DataTable objTable)
		{
			bool result = false;
			for (int i = 0; i < objTable.Rows.Count; i++)
			{
				if (objTable.Rows[i]["ProductName"].ToString().ToUpper() == pName.ToUpper())
				{
					result = true;
				}
			}
			return result;
		}
		private string numOutput(string PID, string Packet, string currWhID)
		{
			string text = "0";
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			sqlConnection.Open();
			SqlCommand sqlCommand = sqlConnection.CreateCommand();
			sqlCommand.CommandText = string.Concat(new string[]
			{
				"SELECT SUM([productNumber]/Rate) AS [numOutput] FROM [TabDetailBill],[TabBill] WHERE [TabBill].[WID] = '",
				currWhID,
				"' AND [TabDetailBill].[billID] = [TabBill].[ID] AND [TypeInput] IN ('OUT1','OUT2','OUT3','OUT4') AND [TabDetailBill].[PID] = ",
				PID,
				" AND [TabDetailBill].[Packet] = '",
				Packet,
				"'"
			});
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					text = sqlDataReader["numOutput"].ToString();
				}
			}
			else
			{
				text = "0";
			}
			sqlDataReader.Close();
			sqlConnection.Close();
			sqlConnection.Dispose();
			if (text.Trim() == "")
			{
				text = "0";
			}
			return text;
		}
		private string numIntput(string PID, string Packet)
		{
			string text = "0";
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			sqlConnection.Open();
			SqlCommand sqlCommand = sqlConnection.CreateCommand();
			sqlCommand.CommandText = string.Concat(new string[]
			{
				"SELECT SUM([productNumber]) AS [numOutput] FROM [TabDetailBill],[TabBill] WHERE [TabDetailBill].[billID] = [TabBill].[ID] AND [TypeInput] IN ('IN1','IN2','IN3') AND [TabDetailBill].[PID] = ",
				PID,
				" AND [TabDetailBill].[Packet] = '",
				Packet,
				"'"
			});
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					text = sqlDataReader["numOutput"].ToString();
				}
			}
			else
			{
				text = "0";
			}
			sqlDataReader.Close();
			sqlConnection.Close();
			sqlConnection.Dispose();
			if (text.Trim() == "")
			{
				text = "0";
			}
			return text;
		}
		public float balancerOfPack(string Pid, string Packet, string currWID)
		{
			float result = 0f;
			try
			{
				SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
				sqlConnection.Open();
				SqlCommand sqlCommand = sqlConnection.CreateCommand();
				sqlCommand.CommandText = string.Concat(new string[]
				{
					"SELECT * FROM [TabTotalBalancer] WHERE [Pid] = ",
					Pid,
					" AND [Packet] = '",
					Packet,
					"' AND StockID = '",
					currWID,
					"'"
				});
				SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
				while (sqlDataReader.Read())
				{
					result = float.Parse(sqlDataReader["productNumberIn"].ToString()) - float.Parse(sqlDataReader["productNumberOut"].ToString());
				}
				sqlDataReader.Close();
				sqlConnection.Close();
				sqlConnection.Dispose();
			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message);
				result = 0f;
			}
			return result;
		}
		public float balancerOfAllPack(string Pid, string currWID)
		{
			float num = 0f;
			try
			{
				SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
				sqlConnection.Open();
				SqlCommand sqlCommand = sqlConnection.CreateCommand();
				sqlCommand.CommandText = string.Concat(new string[]
				{
					"SELECT productNumberIn, productNumberOut FROM [TabTotalBalancer] WHERE [Pid] = ",
					Pid,
					" AND [StockID] = '",
					currWID,
					"'"
				});
				SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
				while (sqlDataReader.Read())
				{
					num += float.Parse(sqlDataReader["productNumberIn"].ToString()) - float.Parse(sqlDataReader["productNumberOut"].ToString());
				}
				sqlDataReader.Close();
				sqlConnection.Close();
				sqlConnection.Dispose();
			}
			catch
			{
				num = 0f;
			}
			return num;
		}
		public float balancerOfAllPack(string Pid)
		{
			float num = 0f;
			try
			{
				SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
				sqlConnection.Open();
				SqlCommand sqlCommand = sqlConnection.CreateCommand();
				sqlCommand.CommandText = "SELECT * FROM [TabTotalBalancer] WHERE [Pid] = " + Pid;
				SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
				while (sqlDataReader.Read())
				{
					num += float.Parse(sqlDataReader["productNumberIn"].ToString()) - float.Parse(sqlDataReader["productNumberOut"].ToString());
				}
				sqlDataReader.Close();
				sqlConnection.Close();
				sqlConnection.Dispose();
			}
			catch
			{
				num = 0f;
			}
			return num;
		}
		public int getCount()
		{
			int result = 0;
			try
			{
				SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
				sqlConnection.Open();
				string cmdText = "SELECT COUNT(*) AS Countproducts FROM [TabProducts]";
				SqlCommand sqlCommand = new SqlCommand(cmdText, sqlConnection);
				SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
				while (sqlDataReader.Read())
				{
					result = int.Parse(sqlDataReader["Countproducts"].ToString());
				}
				sqlDataReader.Close();
				sqlDataReader.Dispose();
				sqlConnection.Close();
				sqlConnection.Dispose();
			}
			catch
			{
			}
			return result;
		}
		public void getInformationFromBarcodeForWard(string barcode, ref string currPid, TextBox txtProductName, TextBox txtProductUnit, TextBox txtProductPrice, TextBox txtProductPrice1, TextBox txtProductPrice2, ComboBox cbbNational, TextBox txtProductNumber, TextBox txtBalancer, string currWID, bool BasePrice)
		{
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			SqlCommand sqlCommand = new SqlCommand();
			sqlCommand.Connection = sqlConnection;
			sqlCommand.CommandText = string.Concat(new string[]
			{
				"SELECT DISTINCT [TabProducts].[Pid], [TabProducts].[P_Name], [TabDetailBill].[productUnit], [TabDetailBill].[Packet] FROM [TabDetailBill],[TabProducts] WHERE [TabProducts].[Pid] = [TabDetailBill].[Pid] AND ([TabDetailBill].[Barcode] = '",
				barcode,
				"' OR [TabDetailBill].[Barcode] = '0",
				barcode,
				"') AND [TabDetailBill].[ProductPrice] > 0"
			});
			sqlConnection.Open();
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			while (sqlDataReader.Read())
			{
				currPid = sqlDataReader["PID"].ToString();
				txtProductName.Text = sqlDataReader["P_Name"].ToString();
				txtProductUnit.Text = sqlDataReader["productUnit"].ToString();
				try
				{
					if (BasePrice)
					{
						txtProductPrice.Text = this.getPriceInCurrent(currPid).ToString();
						txtProductPrice1.Text = this.getPriceOutBase(currPid, sqlDataReader["Packet"].ToString()).ToString();
						txtProductPrice2.Text = this.getPriceOutRetailCurrent(currPid).ToString();
					}
					else
					{
						txtProductPrice.Text = this.getPriceInAVG(currPid).ToString();
						txtProductPrice1.Text = this.getPriceOutputAVG(currPid).ToString();
						txtProductPrice2.Text = this.getPriceOutRetailAVG(currPid).ToString();
					}
					cbbNational.SelectedValue = this.getNationalID(currPid, sqlDataReader["Packet"].ToString()).ToString();
					txtBalancer.Text = this.balancerOfPack(currPid, sqlDataReader["Packet"].ToString(), currWID).ToString();
				}
				catch
				{
					txtProductName.Text = "";
					txtProductUnit.Text = "";
					MessageBox.Show("Không tìm thấy lô hàng nào của sản phẩm được chọn\r\n\r\nChỉ những sản phẩm có số lô cụ thể mới được xuất kho", "Cảnh báo", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
					return;
				}
				txtProductNumber.Text = "1";
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
		}
		public void setBarcode(string p_ID, string barcode)
		{
			try
			{
				SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
				SqlCommand sqlCommand = new SqlCommand();
				sqlCommand.Connection = sqlConnection;
				sqlCommand.CommandText = "UPDATE [TabProducts] SET [BarCode] = '" + barcode + "' WHERE [PID] = " + p_ID;
				sqlConnection.Open();
				sqlCommand.ExecuteNonQuery();
				sqlConnection.Close();
				sqlConnection.Dispose();
			}
			catch
			{
			}
		}
		public void getInformationFromBarcodeForInput(string barcode, ref string currPid, TextBox txtProductName, TextBox txtProductUnit, Label lblPossName, ref string txtProductPrice, ref string txtProductPrice1, ref string txtProductPrice2)
		{
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			SqlCommand sqlCommand = new SqlCommand();
			sqlCommand.Connection = sqlConnection;
			sqlCommand.CommandText = string.Concat(new string[]
			{
				"SELECT DISTINCT [TabProducts].[Pid], [TabProducts].[P_Name], [TabProducts].[possName], [TabProducts].[P_PriceBase], [TabProducts].[P_PriceTrade], [TabProducts].[P_PriceRetail], [TabUnitProduct].[UnitName] FROM [TabProducts],[TabUnitProduct] WHERE [TabProducts].[P_DonViTinh] = [TabUnitProduct].[UnitID] AND ([TabProducts].[BarCode] = '",
				barcode,
				"' OR [TabProducts].[BarCode] = '0",
				barcode,
				"')"
			});
			sqlConnection.Open();
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			while (sqlDataReader.Read())
			{
				currPid = sqlDataReader["PID"].ToString();
				txtProductName.Text = sqlDataReader["P_Name"].ToString();
				txtProductUnit.Text = sqlDataReader["UnitName"].ToString();
				lblPossName.Text = sqlDataReader["possName"].ToString();
				txtProductPrice = sqlDataReader["P_PriceBase"].ToString();
				txtProductPrice1 = sqlDataReader["P_PriceTrade"].ToString();
				txtProductPrice2 = sqlDataReader["P_PriceRetail"].ToString();
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
		}
		public void getInformationFromBarcode(string barcode, ref string currPid, TextBox txtProductName, TextBox txtProductUnit, TextBox txtProductPrice, ComboBox cbbNational, TextBox txtProductNumber, TextBox txtBalancer, string currWID, bool BasePrice)
		{
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			SqlCommand sqlCommand = new SqlCommand();
			sqlCommand.Connection = sqlConnection;
			sqlCommand.CommandText = string.Concat(new string[]
			{
				"SELECT DISTINCT [TabProducts].[Pid], [TabProducts].[P_Name], [TabDetailBill].[productUnit], [TabDetailBill].[Packet] FROM [TabDetailBill],[TabProducts] WHERE [TabProducts].[Pid] = [TabDetailBill].[Pid] AND ([TabDetailBill].[Barcode] = '",
				barcode,
				"' OR [TabDetailBill].[Barcode] = '0",
				barcode,
				"') AND [TabDetailBill].[ProductPrice] > 0"
			});
			sqlConnection.Open();
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			while (sqlDataReader.Read())
			{
				currPid = sqlDataReader["PID"].ToString();
				txtProductName.Text = sqlDataReader["P_Name"].ToString();
				txtProductUnit.Text = sqlDataReader["productUnit"].ToString();
				try
				{
					if (BasePrice)
					{
						txtProductPrice.Text = this.getPriceOutBase(currPid, sqlDataReader["Packet"].ToString()).ToString();
					}
					else
					{
						txtProductPrice.Text = this.getPriceOutputAVG(currPid).ToString();
					}
					cbbNational.SelectedValue = this.getNationalID(currPid, sqlDataReader["Packet"].ToString()).ToString();
					txtBalancer.Text = this.balancerOfPack(currPid, sqlDataReader["Packet"].ToString(), currWID).ToString();
				}
				catch
				{
					txtProductName.Text = "";
					txtProductUnit.Text = "";
					MessageBox.Show("Không tìm thấy lô hàng nào của sản phẩm được chọn\r\n\r\nChỉ những sản phẩm có số lô cụ thể mới được xuất kho", "Cảnh báo", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
					return;
				}
				txtProductNumber.Text = "1";
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
		}
		public bool getInformationFromBarcodeRetail(TextBox txtBarcode, ref string currPid, TextBox txtProductName, TextBox txtProductUnit, TextBox txtProductPrice, ComboBox cbbNational, TextBox txtProductNumber, TextBox txtBalancer, string currWID, bool BasePrice)
		{
			bool result = false;
			try
			{
				SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
				SqlCommand sqlCommand = new SqlCommand();
				sqlCommand.Connection = sqlConnection;
				sqlCommand.CommandText = string.Concat(new string[]
				{
					"SELECT DISTINCT [TabProducts].[Pid], [TabProducts].[P_Name], [TabDetailBill].[productUnit], [TabDetailBill].[Packet] FROM [TabDetailBill],[TabProducts] WHERE [TabProducts].[Pid] = [TabDetailBill].[Pid] AND ([TabDetailBill].[Barcode] = '",
					txtBarcode.Text,
					"' OR [TabDetailBill].[Barcode] = '0",
					txtBarcode.Text,
					"') AND ([TabDetailBill].[ProductPrice] > 0) AND BillID IN (SELECT ID FROM TabBill WHERE TypeInput IN ('IN1','IN2','IN3')) AND [dbo].[uf_getBalancerProductByPacket]([TabProducts].[Pid],Packet) > 0 ORDER BY [P_Name]"
				});
				sqlConnection.Open();
				SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
				while (sqlDataReader.Read())
				{
					currPid = sqlDataReader["PID"].ToString();
					txtProductName.Text = sqlDataReader["P_Name"].ToString();
					txtProductUnit.Text = sqlDataReader["productUnit"].ToString();
					try
					{
						if (BasePrice)
						{
							txtProductPrice.Text = this.getPriceOutRetailBase(currPid, sqlDataReader["Packet"].ToString()).ToString();
						}
						else
						{
							txtProductPrice.Text = this.getPriceOutRetailAVG(currPid).ToString();
						}
						cbbNational.SelectedValue = this.getNationalID(currPid, sqlDataReader["Packet"].ToString()).ToString();
						txtBalancer.Text = this.balancerOfPack(currPid, sqlDataReader["Packet"].ToString(), currWID).ToString();
					}
					catch
					{
						result = false;
						txtProductName.Text = "";
						txtProductUnit.Text = "";
					}
					txtProductNumber.Text = "1";
					result = true;
				}
				sqlDataReader.Close();
				sqlDataReader.Dispose();
				sqlConnection.Close();
				sqlConnection.Dispose();
			}
			catch
			{
				result = false;
				MessageBox.Show("Không tìm thấy sản phẩm có mã vạch đã nhập !", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
			}
			return result;
		}
		public void forWardPossition(string Pid, string possID, string PossName)
		{
			try
			{
				SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
				sqlConnection.Open();
				string cmdText = string.Concat(new string[]
				{
					"UPDATE [TabProducts] SET [possID] = ",
					possID,
					", [possName] = N'",
					PossName,
					"' WHERE [Pid] = ",
					Pid
				});
				SqlCommand sqlCommand = new SqlCommand(cmdText, sqlConnection);
				sqlCommand.ExecuteNonQuery();
				string cmdText2 = "UPDATE [TabDetailBill] SET [possName] = N'" + PossName + "' WHERE [Pid] = " + Pid;
				SqlCommand sqlCommand2 = new SqlCommand(cmdText2, sqlConnection);
				sqlCommand2.ExecuteNonQuery();
				string cmdText3 = "UPDATE [TabDetailBillDeleted] SET [possName] = N'" + PossName + "' WHERE [Pid] = " + Pid;
				SqlCommand sqlCommand3 = new SqlCommand(cmdText3, sqlConnection);
				sqlCommand3.ExecuteNonQuery();
				sqlConnection.Close();
				sqlConnection.Dispose();
			}
			catch
			{
			}
		}
		public double getVATofProduct(string pid, string packetID)
		{
			double result = 0.0;
			try
			{
				SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
				sqlConnection.Open();
				string cmdText = string.Concat(new string[]
				{
					"SELECT VAT FROM TabDetailBill WHERE Packet = '",
					packetID,
					"' AND Pid = ",
					pid,
					" AND billID IN (SELECT ID FROM TabBill WHERE TypeInput = 'IN1')"
				});
				SqlCommand sqlCommand = new SqlCommand(cmdText, sqlConnection);
				SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
				while (sqlDataReader.Read())
				{
					result = double.Parse(sqlDataReader["VAT"].ToString());
				}
				sqlDataReader.Close();
				sqlDataReader.Dispose();
				sqlConnection.Close();
				sqlConnection.Dispose();
			}
			catch
			{
			}
			return result;
		}
	}
}
